package com.yonyou.cn.excel;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.CellRangeAddress;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

import java.io.*;
import java.math.BigDecimal;

public class HbCell {

    public static void main(String[] args) throws Exception {
        HSSFWorkbook workbook = new HSSFWorkbook();
        getHeader2(workbook);

       String aa="整体实力:10;价格水平:10;协同能力:10;供应份额:10;用户分布:10;产品研发与创新能力:10;整体实力:10;";
       String[] split = aa.split(";");
       for(String s:split){
           String[] item = s.split(":");
           System.out.println(item[0]+"("+item[1]+")");
       }
        //setHeader1();
//        BigDecimal b = new BigDecimal("11");
//        BigDecimal multiply = b.divide(new BigDecimal("100")).multiply(new BigDecimal(2.4).multiply(new BigDecimal(20)));
//        System.out.println(multiply.setScale( 0, BigDecimal.ROUND_HALF_UP ));

    }



    private static void setHeader1() throws Exception {
        File file = new File("D:\\demo.xls");
        FileInputStream in = new FileInputStream(file);
        HSSFWorkbook workbook = new HSSFWorkbook(in);
        HSSFSheet sheetAt = workbook.getSheetAt(0);
        for(int i=2;i<=sheetAt.getLastRowNum(); i++){
            Row row = sheetAt.getRow(i);
            resolveUnitScore( row);
        }


    }

    public static  void resolveUnitScore(Row row){
        Cell suplierCodeCell = row.getCell(0);
        Cell suplierNameCell = row.getCell(1);
        Cell commdityCodeCell = row.getCell(2);
        Cell purchaseModeCell = row.getCell(3);
        Cell  smallOrgScoreCell=row.getCell(4);
        Cell owerallStrengthCell = row.getCell(5);
        Cell priceLevelCell = row.getCell(6);
        Cell collaborativeAbilityCell = row.getCell(7);
        Cell userDistributionCell = row.getCell(8);
        Cell innovationAbilityCell = row.getCell(9);

        String suplierCode = "";
        String suplierName = "";
        String commdityCode  = "";
        String purchaseMode="";
        String smallOrgScore  = "";
        String owerallStrength  = "";
        String priceLevel="";
        String collaborativeAbility="";
        String userDistribution = "";
        String innovationAbility = "";

        if (suplierCodeCell != null) {
            suplierCodeCell.setCellType(Cell.CELL_TYPE_STRING);
            suplierCode = suplierCodeCell.getStringCellValue();
        }
        if (suplierNameCell != null) {
            suplierNameCell.setCellType(Cell.CELL_TYPE_STRING);
            suplierName = suplierNameCell.getStringCellValue();
        }
        if (commdityCodeCell != null) {
            commdityCodeCell.setCellType(Cell.CELL_TYPE_STRING);
            commdityCode = commdityCodeCell.getStringCellValue();
        }
        if (purchaseModeCell != null) {
            purchaseModeCell.setCellType(Cell.CELL_TYPE_STRING);
            purchaseMode = purchaseModeCell.getStringCellValue();
        }
        if (smallOrgScoreCell != null) {
            smallOrgScoreCell.setCellType(Cell.CELL_TYPE_STRING);
            smallOrgScore = smallOrgScoreCell.getStringCellValue();
        }
        if (owerallStrengthCell != null) {
            owerallStrengthCell.setCellType(Cell.CELL_TYPE_STRING);
            owerallStrength = owerallStrengthCell.getStringCellValue();
        }
        if (priceLevelCell != null) {
            priceLevelCell.setCellType(Cell.CELL_TYPE_STRING);
            priceLevel = priceLevelCell.getStringCellValue();
        }
        if (collaborativeAbilityCell != null) {
            collaborativeAbilityCell.setCellType(Cell.CELL_TYPE_STRING);
            collaborativeAbility = collaborativeAbilityCell.getStringCellValue();
        }
        if (userDistributionCell != null) {
            userDistributionCell.setCellType(Cell.CELL_TYPE_STRING);
            userDistribution = userDistributionCell.getStringCellValue();
        }

        if (innovationAbilityCell != null) {
            innovationAbilityCell.setCellType(Cell.CELL_TYPE_STRING);
            innovationAbility = innovationAbilityCell.getStringCellValue();
        }


    }

    private static void getHeader1(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFSheet sheet = workbook.createSheet("sheet");
        sheet.setDefaultColumnWidth(20);
        HSSFRow row0 = sheet.createRow(0);
        row0.setHeight((short)500);
        HSSFCell cell_00 = row0.createCell(0);
        cell_00.setCellStyle(style);
        cell_00.setCellValue("供应商编码");
        HSSFCell cell_01 = row0.createCell(1);
        cell_01.setCellStyle(style);
        cell_01.setCellValue("供应商名称");
        HSSFCell cell_02 = row0.createCell(2);
        cell_02.setCellStyle(style);
        cell_02.setCellValue("物资编码（8位码）");
        HSSFCell cell_03 = row0.createCell(3);
        cell_03.setCellStyle(style);
        cell_03.setCellValue("集中采购方式（带量/定商定价/定商）");
        HSSFCell cell_04 = row0.createCell(4);
        cell_04.setCellStyle(style);
        cell_04.setCellValue("考评成绩");
        HSSFCell cell_05 = row0.createCell(5);
        cell_05.setCellStyle(style);
        cell_05.setCellValue("考评内容及得分");


        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell_20 = row2.createCell(5);
        cell_20.setCellStyle(style);
        cell_20.setCellValue("整体实力");
        HSSFCell cell_21 = row2.createCell(6);
        cell_21.setCellStyle(style);
        cell_21.setCellValue("价格水平");
        HSSFCell cell_22 = row2.createCell(7);
        cell_22.setCellStyle(style);
        cell_22.setCellValue("协同能力");
        HSSFCell cell_23 = row2.createCell(8);
        cell_23.setCellStyle(style);
        cell_23.setCellValue("供应份额");
        HSSFCell cell_24 = row2.createCell(9);
        cell_24.setCellStyle(style);
        cell_24.setCellValue("用户分布");
        HSSFCell cell_25 = row2.createCell(10);
        cell_25.setCellStyle(style);
        cell_25.setCellValue("产品研发与创新能力");

        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        // 行和列都是从0开始计数，且起始结束都会合并
        CellRangeAddress region1 = new CellRangeAddress(0, 0, 5, 10);
        CellRangeAddress region2 = new CellRangeAddress(0, 1, 0, 0);
        CellRangeAddress region3 = new CellRangeAddress(0, 1, 1, 1);
        CellRangeAddress region4 = new CellRangeAddress(0, 1, 2, 2);
        CellRangeAddress region5 = new CellRangeAddress(0, 1, 3, 3);
        CellRangeAddress region6 = new CellRangeAddress(0, 1, 4, 4);
        sheet.addMergedRegion(region1);
        sheet.addMergedRegion(region2);
        sheet.addMergedRegion(region3);
        sheet.addMergedRegion(region4);
        sheet.addMergedRegion(region5);
        sheet.addMergedRegion(region6);
//        File file = new File("D:\\demo.xls");
        File file = new File("/Users/zhengchenggong/Desktop/window/a/demo.xls");
        try {
            FileOutputStream fout = new FileOutputStream(file);
            workbook.write(fout);
            fout.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    private static void getHeader2(HSSFWorkbook workbook) {
        HSSFCellStyle style = workbook.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        HSSFSheet sheet = workbook.createSheet("sheet");
        sheet.setDefaultColumnWidth(20);
        HSSFRow row0 = sheet.createRow(0);
        row0.setHeight((short)500);

        HSSFCell cell_00 = row0.createCell(0);
        cell_00.setCellStyle(style);
        cell_00.setCellValue("供应商编码");
        HSSFCell cell_01 = row0.createCell(1);
        cell_01.setCellStyle(style);
        cell_01.setCellValue("供应商名称");

        HSSFCell cell_02 = row0.createCell(2);
        cell_02.setCellStyle(style);
        cell_02.setCellValue("物资编码（8位码）");
        HSSFCell cell_03 = row0.createCell(3);
        cell_03.setCellStyle(style);
        cell_03.setCellValue("物质名称");
       /* HSSFCell cell_04 = row0.createCell(4);
        cell_04.setCellStyle(style);
        cell_04.setCellValue("考评成绩");
        HSSFCell cell_05 = row0.createCell(7);
        cell_05.setCellStyle(style);
        cell_05.setCellValue("考评结果\n"+"(合格/不合格)");
        HSSFCell cell_06 = row0.createCell(8);
        cell_06.setCellStyle(style);
        cell_06.setCellValue("供应商分级\n" + "(A/B/C)");
        HSSFRow row2 = sheet.createRow(1);
        HSSFCell cell_20 = row2.createCell(4);
        cell_20.setCellStyle(style);
        cell_20.setCellValue("一单一评（60%）");
        HSSFCell cell_21 = row2.createCell(5);
        cell_21.setCellStyle(style);
        cell_21.setCellValue("年度考评（40%）");
        HSSFCell cell_22 = row2.createCell(6);
        cell_22.setCellStyle(style);
        cell_22.setCellValue("总成绩");*/
        // 合并日期占两行(4个参数，分别为起始行，结束行，起始列，结束列)
        // 行和列都是从0开始计数，且起始结束都会合并
        CellRangeAddress region1 = new CellRangeAddress(0, 0, 4, 6);
        CellRangeAddress region2 = new CellRangeAddress(0, 1, 0, 0);
        CellRangeAddress region3 = new CellRangeAddress(0, 1, 1, 1);
        CellRangeAddress region4 = new CellRangeAddress(0, 1, 2, 2);
        CellRangeAddress region5 = new CellRangeAddress(0, 1, 3, 3);
        CellRangeAddress region6 = new CellRangeAddress(0, 1, 7, 7);
        CellRangeAddress region7 = new CellRangeAddress(0, 1, 8, 8);
        sheet.addMergedRegion(region1);
        sheet.addMergedRegion(region2);
        sheet.addMergedRegion(region3);
        sheet.addMergedRegion(region4);
        sheet.addMergedRegion(region5);
        sheet.addMergedRegion(region6);
        sheet.addMergedRegion(region7);



        int i=2;
        for(int j=0;j<5;j++){
            HSSFRow row = sheet.createRow(i);
            HSSFCell cell0 = row.createCell(0);
            cell0.setCellStyle(style);
            cell0.setCellValue("11111111");
            HSSFCell cell1 = row.createCell(1);
            cell1.setCellStyle(style);
            cell1.setCellValue("22222222");

            HSSFCell cell2 = row.createCell(2);
            cell2.setCellStyle(style);
            cell2.setCellValue("3333333333");
            HSSFCell cell3 = row.createCell(3);
            cell3.setCellStyle(style);
            cell3.setCellValue("444444444444444");
            HSSFCell cell4 = row.createCell(4);
            cell4.setCellStyle(style);
            cell4.setCellValue("5555555555555555");
            HSSFCell cell5 = row.createCell(5);
            cell5.setCellStyle(style);
            cell5.setCellValue("66666666666666666666666");
            HSSFCell cell6 = row.createCell(6);
            cell6.setCellStyle(style);
            cell6.setCellValue("7777777777777777777777777");
            i++;

        }


        File file = new File("/Users/zhengchenggong/Desktop/demo.xls");
        try {
            FileOutputStream fout = new FileOutputStream(file);
            workbook.write(fout);
            fout.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}
